-- Filename: les03_staticsql_demo.sql
-- Created: September 8, 2007
-- Creator: Jenny Tsai-Smith (str2list function code from Tom Kyte)
-- Description: Static SQL code sample for handling varying number of IN-list
--              values in query condition.

CONN hr

SET SERVEROUTPUT ON

SELECT d.department_name, l.city, c.country_name
   FROM departments d, locations l, countries c
   WHERE d.location_id = l.location_id
   AND c.country_id = l.country_id
   AND c.country_name in ('Germany','Canada')
/

SELECT d.department_name, l.city, c.country_name
   FROM departments d, locations l, countries c
   WHERE d.location_id = l.location_id
   AND c.country_id = l.country_id
   AND c.country_name in ('Germany','Canada','United Kingdom')
/

CREATE OR REPLACE PROCEDURE show_dept_loc_concat 
(p_countries IN VARCHAR2)
AS
  TYPE cv_infotyp IS REF CURSOR;
  cv   cv_infotyp;
  v_sql_stmt VARCHAR2(4000);
  v_dept departments.department_name%TYPE;
  v_city locations.city%TYPE;
  v_country countries.country_name%TYPE;
BEGIN

  v_sql_stmt := 'SELECT d.department_name, l.city, c.country_name
   FROM departments d, locations l, countries c
   WHERE d.location_id = l.location_id
   AND c.country_id = l.country_id
   AND c.country_name in (' || p_countries ||')';

  OPEN cv FOR v_sql_stmt;
  LOOP
      FETCH cv INTO v_dept, v_city, v_country;
      EXIT WHEN cv%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE('Dept Info: '||v_dept ||
                       ', ' || v_city || ', ' || v_country);
  END LOOP;
  CLOSE cv;
EXCEPTION WHEN OTHERS THEN
   dbms_output.PUT_LINE(sqlerrm);
   dbms_output.PUT_LINE('SQL statement: ' || v_sql_stmt);
END;
/

EXEC show_dept_loc_concat('''Canada'',''Germany''')

CREATE OR REPLACE PROCEDURE show_dept_loc_dynamicsql 
(p_countries IN VARCHAR2)
AS
  TYPE cv_infotyp IS REF CURSOR;
  cv   cv_infotyp;
  v_sql_stmt VARCHAR2(500);
  v_dept departments.department_name%TYPE;
  v_city locations.city%TYPE;
  v_country countries.country_name%TYPE;
BEGIN

  v_sql_stmt := 'SELECT d.department_name, l.city, c.country_name
   FROM departments d, locations l, countries c
   WHERE d.location_id = l.location_id
   AND c.country_id = l.country_id
   AND c.country_name in (:p_countries)';

  OPEN cv FOR v_sql_stmt USING p_countries;
  LOOP
      FETCH cv INTO v_dept, v_city, v_country;
      EXIT WHEN cv%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE('Dept Info: '||v_dept ||
                       ', ' || v_city || ', ' || v_country);
  END LOOP;
  CLOSE cv;
EXCEPTION WHEN OTHERS THEN
   dbms_output.PUT_LINE(sqlerrm);
   dbms_output.PUT_LINE('SQL statement: ' || v_sql_stmt);
END;
/

EXEC show_dept_loc_dynamicsql('''Canada'',''Germany''')

CREATE OR REPLACE TYPE str2tblType
AS TABLE OF VARCHAR2(4000)
/

CREATE OR REPLACE FUNCTION str2list
    ( p_str IN VARCHAR2,
      p_delim IN VARCHAR2 DEFAULT ',' )
RETURN str2tblType
AS
l_str LONG DEFAULT p_str || p_delim;
l_n   NUMBER;
l_data str2tblType := str2tblType();
BEGIN
  LOOP
    l_n := INSTR( l_str, p_delim );
    EXIT WHEN (NVL(l_n,0) = 0);
    l_data.EXTEND;
    l_data(l_data.COUNT) := LTRIM(RTRIM(SUBSTR(l_str,1,l_n-1)));
    l_str := SUBSTR( l_str, l_n+1 );
  END LOOP;
  RETURN l_data;
END;
/

CREATE OR REPLACE PROCEDURE show_dept_loc_staticsql 
(p_countries IN VARCHAR2)
AS
BEGIN
FOR i IN 
  (SELECT d.department_name, l.city, c.country_name
   FROM departments d, locations l, countries c
   WHERE d.location_id = l.location_id
   AND c.country_id = l.country_id
   AND c.country_name in 
     (SELECT column_value FROM TABLE(str2list(p_countries))))
  LOOP
      DBMS_OUTPUT.PUT_LINE('Dept Info: '||i.department_name ||
                       ', ' || i.city || ', ' || i.country_name);
  END LOOP;
END;
/

EXEC show_dept_loc_staticsql('Canada, Germany')

EXEC show_dept_loc_staticsql('Canada, United Kingdom, Germany')